package cn.com.blueInfo.business.lottery.testFunc;

import lombok.extern.log4j.Log4j2;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

@Log4j2
@Component
public class AlterTableColumn {

    private static final String SCHEMA_NAME = "suxch";

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void handler() {
        List<Map<String, Object>> tableMetaList = queryTableMetadata();
        for (Map<String, Object> oneRow : tableMetaList) {
            String tableName = (String) oneRow.get("table_name");
            int tableNameSize = tableName.split("_").length;
            if (tableName.contains("sports") && tableNameSize == 4) {
                alterColumnType(tableName);
            }
        }
    }

    private void alterColumnType(String tableName) {
        String alterSql = "delete \n" +
                "FROM " + tableName + " \n" +
                "WHERE\n" +
                "  (red2 = red1 + 1 AND red3 = red2 + 1) OR\n" +
                "  (red3 = red2 + 1 AND red4 = red3 + 1) OR\n" +
                "  (red4 = red3 + 1 AND red5 = red4 + 1) OR\n" +
                "  (red2 = red1 + 1 AND red3 = red2 + 1 AND red4 = red3 + 1) OR\n" +
                "  (red3 = red2 + 1 AND red4 = red3 + 1 AND red5 = red4 + 1) OR\n" +
                "  (red2 = red1 + 1 AND red3 = red2 + 1 AND red4 = red3 + 1 AND red5 = red4 + 1);";
        jdbcTemplate.execute(alterSql);
        log.info("执行完成：{}", alterSql);
    }

    /**
     * 查询数据库表元数据（表名和表注释）
     */
    private List<Map<String, Object>> queryTableMetadata() {
        String sql = "select table_name, table_comment from information_schema.`TABLES` where TABLE_SCHEMA = ?";
        return jdbcTemplate.queryForList(sql, SCHEMA_NAME);
    }

}
